CREATE FUNCTION dbo.fn_asi_GetFullName(
     @FirstName varchar(20) = NULL,
     @MiddleName varchar(20) = NULL,
     @LastName varchar(30) = NULL,
     @Prefix varchar(25) = NULL,
     @Suffix varchar(10) = NULL,
     @Designation varchar(20) = NULL,
     @Title varchar(80) = NULL,
     @Informal varchar(20) = NULL)
     returns varchar(70)
AS
BEGIN
     DECLARE @Result varchar(255)
     DECLARE @FullName varchar(70)
     SET @Result = ''
     -- By default, FullName is just FirstName + LastName
     IF(@Prefix IS NOT NULL AND LEN(@Prefix) > 0)
        SET @Result = RTRIM(LTRIM(@Prefix))
     IF(@FirstName IS NOT NULL AND Len(@FirstName) > 0)
        SET @Result = @Result + ' ' + RTRIM(LTRIM(@FirstName))
     IF(@MiddleName IS NOT NULL AND Len(@MiddleName) > 0)
        SET @Result = @Result + ' ' + RTRIM(LTRIM(@MiddleName))
     IF(@LastName IS NOT NULL AND Len(@LastName) > 0)
        SET @Result = @Result + ' ' + RTRIM(LTRIM(@LastName))
     IF(@Suffix IS NOT NULL AND Len(@Suffix) > 0)
        SET @Result = @Result + ', ' + RTRIM(LTRIM(@Suffix))
     IF(@Designation IS NOT NULL AND Len(@Designation) > 0)
        SET @Result = @Result + ', ' + RTRIM(LTRIM(@Designation))
     SET @Result = RTRIM(LTRIM(@Result))
     IF(LEFT(@Result,1) = ',')
        SET @Result = LTRIM(RIGHT(@Result,Len(@Result)-2))
     SET @FullName = LEFT(@Result,70)
    RETURN @FullName
END

GO
GRANT EXECUTE ON  [dbo].[fn_asi_GetFullName] TO [IMIS]
GO